 -- **************************************************************************
--    Project Name:   经营指标-异常件-派件刷单监控
--    Job Name:       jms_dm.dm_end_dispatch_cheat_swip_detail_dt
--    Author :        季修魁
--    date：          2023/06/01
-- **************************************************************************
-- **************************************************************************

set hive.exec.dynamic.partition=true; -- 动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=500; -- 每天生成 60 个分区
set hive.exec.max.dynamic.partitions.pernode=500; -- 每天生成 60 个分区

---运单信息
with tab_waybill as (
select 
waybill_no
,sign_time
,pick_network_code
,pick_network_name
,sign_network_code
,sign_network_name
,dispatch_network_code
,dispatch_network_name
,receiver_name
,receiver_mobile_phone
,receiver_province_id
,receiver_city_id
,receiver_area_id
,receiver_detailed_address
,substr(receiver_mobile_phone,1,3)  as mobile_phone_bf_trd
,substr(receiver_mobile_phone,8,4)  as mobile_phone_lst_fth
  from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
 where dt>= date_sub('{{ execution_date | cst_ds }}',31) and dt <='{{ execution_date | cst_ds }}'
   and to_date(sign_time) ='{{ execution_date | cst_ds }}' 
   and if(regexp_extract(receiver_mobile_phone, '[\u4e00-\u9fa5]+', 0)='',0,1) = 0    ---剔除汉字
   and length(receiver_mobile_phone)=11
   and substr(receiver_mobile_phone,8,4)<>'****'
 ),

---异常件 刷单大于等于500  (手机号大于500票)

tab_waybill_overmobile as  (
select receiver_mobile_phone,count(waybill_no) as bill_cnt
  from tab_waybill
 group by receiver_mobile_phone
 having count(waybill_no) >=500
)

insert overwrite table jms_dm.dm_end_dispatch_cheat_swip_detail_dt 
select  t1.waybill_no
       ,t1.sign_time
       ,t1.pick_network_code
       ,t1.pick_network_name
       ,net1.agent_code
       ,net1.agent_name
       ,t1.sign_network_code
       ,t1.sign_network_name
       ,net2.agent_code
       ,net2.agent_name
       ,t1.dispatch_network_code
       ,t1.dispatch_network_name
       ,net3.agent_code
       ,net3.agent_name
       ,t1.receiver_name             
       ,t1.receiver_mobile_phone     
       ,city_pro.provider_desc         
       ,city_pro.provider_desc             
       ,city_pro.area_desc             
       ,t1.receiver_detailed_address
       ,t1.mobile_phone_bf_trd         
       ,t1.mobile_phone_lst_fth     
       ,to_date(t1.sign_time)     as dt 
  from tab_waybill                            t1
  join tab_waybill_overmobile                 t2
    on t1.receiver_mobile_phone  = t2.receiver_mobile_phone
  left join jms_dim.dim_network_whole_massage net1
    on t1.pick_network_code = net1.code
  left join jms_dim.dim_network_whole_massage net2
    on t1.sign_network_code = net2.code
  left join jms_dim.dim_network_whole_massage net3 
    on t1.dispatch_network_code = net3.code
  left join (
             select area_id
                    ,city_id
                    ,provider_id
                    ,max(provider_desc)  as provider_desc
                    ,max(city_desc)      as city_desc
                    ,max(area_desc)      as area_desc
             from jms_dim.dim_network_whole_massage
            group by area_id,city_id,provider_id  
           ) city_pro
    on t1.receiver_area_id = city_pro.area_id
   and t1.receiver_city_id = city_pro.city_id
   and t1.receiver_province_id = city_pro.provider_id
distribute by pmod(hash(waybill_no), 4)
;